package ch09.person.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import ch09.JDBCFactory;
import ch09.person.domain.Person;

public class PersonDao {
	private Logger logger;
	
	public PersonDao(){
		logger = Logger.getLogger(this.getClass());
	}
	
	/* 사람 여러명 조회 */
	public List<Person> selectAllPerson(){		
		Connection conn = null;
		PreparedStatement pstmt = null;	
		ResultSet rs = null;
		
		String sql = "select id,name from person order by id";		
		List<Person> list = new ArrayList<Person>();
		Person person = null;
		try{
			conn = JDBCFactory.getConnection();
			pstmt = conn.prepareStatement(sql);			
			rs = pstmt.executeQuery();
			while(rs.next()) {
				person = new Person();
				person.setId(rs.getInt(1));
				person.setName(rs.getString(2));
				list.add(person);			
			}
		}catch(Exception e){
			logger.error(e);			
		}finally{
			try{rs.close();}catch(Exception e){}
			try{pstmt.close();}catch(Exception e){}
			try{conn.close();}catch(Exception e){}
		}
		return list;
	}
	
	/* 사람 한명 조회 */
	public Person selectPerson(int id){		
		Connection conn = null;
		PreparedStatement pstmt = null;	
		ResultSet rs = null;
		
		String sql = "select id,name from person where id=?";		
		Person person = new Person();
		try{
			conn = JDBCFactory.getConnection();
			pstmt = conn.prepareStatement(sql);			
			pstmt.setInt(1,id);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				person.setId(rs.getInt("id"));
				person.setName(rs.getString("name"));
			}
		}catch(Exception e){
			logger.error(e);
		}finally{
			try{rs.close();}catch(Exception e){}
			try{pstmt.close();}catch(Exception e){}
			try{conn.close();}catch(Exception e){}
		}
		return person;
	}
	
	
	
	/* 사람 한명 입력 */
	public void insertPerson(Person person){
		Connection conn = null;
		PreparedStatement pstmt = null;		
			
		String sql = "insert into person values(?,?)";
		try{
			conn = JDBCFactory.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,person.getId());
			pstmt.setString(2,person.getName());
			pstmt.executeUpdate();			
		}catch(Exception e){
			logger.error(e);
		}finally{			
			try{pstmt.close();}catch(Exception e){}
			try{conn.close();}catch(Exception e){}
		}		
	}
	
	public void updatePerson(Person person){
		Connection conn = null;
		PreparedStatement pstmt = null;		
			
		String sql = "update person set name=? where id=?";
		try{
			conn = JDBCFactory.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,person.getName());
			pstmt.setInt(2,person.getId());			
			pstmt.executeUpdate();			
		}catch(Exception e){
			logger.error(e);
		}finally{			
			try{pstmt.close();}catch(Exception e){}
			try{conn.close();}catch(Exception e){}
		}		
	}
	
	/* 사람 한명 삭제 */
	public void deletePerson(int id){
		Connection conn = null;
		PreparedStatement pstmt = null;
					
		String sql = "delete person where id=?";
		try{
			conn = JDBCFactory.getConnection();
			pstmt = conn.prepareStatement(sql);			
			pstmt.setInt(1,id);			
			pstmt.executeUpdate();			
		}catch(Exception e){
			logger.error(e);
		}finally{			
			try{pstmt.close();}catch(Exception e){}
			try{conn.close();}catch(Exception e){}
		}		
	}
}
